<?php
namespace AdminBundle\Repository;


class AuthRoleRepository extends BaseRepository
{
    public function pageQuery($_params, $page)
    {
        $sql = <<<EOF
select 
    id, name,note, is_enabled as isEnabled, ctime, create_user as createUser
from auth_role
where 1=1 
EOF;
        $params = array();
        if (!empty($_params)) {
            /*            if (isset($_params['starttime'])) {
                            $sql .= ' and ctime > :starttime ';
                            $params['starttime'] = $_params['starttime'];
                        }
                        if (isset($_params['endtime'])) {
                            $sql .= ' and ctime < :endtime ';
                            $params['endtime'] = $_params['endtime'];
                        }*/
            if (isset($_params['keyword']) && !empty($_params['keyword'])) {
                $sql .= ' and name like :keyword ';
                $params['keyword'] = '%' . $_params['keyword'] . '%';
            }
        }
        $sql .= ' ORDER BY id DESC';

        return $this->pageWithRawSql($sql, $params, $page);
    }

    public function loadRolePermissions($roleId = 0)
    {
        $sql = <<<EOF
SELECT 
    p.id,
    p.name,
    p.content_type_id AS contentTypeId,
    p.codename,
    p.model_method AS modelMethod,
    p.ctime,
    p.create_user AS createUser,
    ct.app_label AS appLabel,
    ct.model,
    IF(lrp.id, 'selected', '') AS selected
FROM
    auth_permission AS p
        LEFT JOIN
    auth_content_type AS ct ON p.content_type_id = ct.id
        AND ct.is_enabled = 1
        LEFT JOIN
    auth_link_role_permissions AS lrp ON p.id = lrp.permission_id
        AND lrp.role_id = :role_id
EOF;
        $params = [];
        $params['role_id'] = $roleId;
        return $this->rawQuery($sql, $params);
    }

    /**
     * 撤消角色已经分配的权限
     * @param $roleId
     * @param array $permissionIds
     * @return bool
     * @throws \Exception
     */
    public function revokeRolePermission($roleId, $permissionIds = [])
    {
        $params = ['role_id' => $roleId];
        $sql = "delete from auth_link_role_permissions where role_id = :role_id ";
        if (!empty($permissionIds)) {
            $sql .= " and permission_id not in (" . implode(",", $permissionIds) . ")";
        }

        return $this->rawExecute($sql, $params);
    }

    /**
     * 关联表中角色与权限联合主键
     * @param $roleId
     * @param $permissionId
     * @return bool
     * @throws \Exception
     */
    public function loadByRoleIdAndPermissionId($roleId, $permissionId)
    {
        $sql = "select * from auth_link_role_permissions where role_id = :role_id and permission_id = :permission_id";
        $params = ['role_id' => $roleId, 'permission_id' => $permissionId];
        return $this->rawQuery($sql, $params);
    }
    
    /**
     * 加载组的角色
     * @param int $groupId
     * @return bool
     * @throws \Exception
     */
    public function loadGroupRoles($groupId = 0)
    {
        $sql = "
SELECT 
    r.id,
    CONCAT(r.name, '|', note) AS name,
    IF(lgr.id, 'selected', '') AS selected
FROM
    auth_role AS r
        LEFT JOIN
    auth_link_group_roles AS lgr ON r.id = lgr.role_id AND lgr.group_id = :groupId
WHERE
    r.is_enabled = 1
";
        $params = ['groupId' => $groupId];
        return $this->rawQuery($sql, $params);
    }

    public function loadUserRoles($groupId = 0)
    {
        $sql = "
SELECT 
    r.id,
    CONCAT(r.name, '|', note) AS name,
    IF(lgr.id, 'selected', '') AS selected
FROM
    auth_role AS r
        LEFT JOIN
    auth_link_group_roles AS lgr ON r.id = lgr.role_id AND lgr.group_id = :groupId
WHERE
    r.is_enabled = 1
";
        $params = ['groupId' => $groupId];
        return $this->rawQuery($sql, $params);
    }
}
